<?php
	class StudentInvoiceController extends Controller{
		private $result = array("actionStatus"=>0,"actionMessage"=>"","model"=>array(),"actionError"=>array());	
		public $mainMenu = "Finance";
		public $subMenu = "";
		public $title = "";
		public $loginUser = ""; 
		public function init() {
			$this->loginUser = LoginUser::getUser();
		}
		
		public function actionIndex(){
			$arrDepartment = MDepartment::model()->findAll();
			$data["arrDepartment"]= $arrDepartment;			 
			$this->layout ="//layouts/ly_blank";
			$this->render('index',$data);
		}
		public function actionInvoiceForm(){
			$op = Yii::app()->request->getParam("op","add");			
			$arrStudent = MStudent::model()->findAll("is_active = 1");
			$acStudent = array();
			foreach ($arrStudent as $student){
				$tmp = array();
				$tmp["label"] = $student->sid ." - " . $student->name;
				$tmp["value"] = $student->sid ." - " . $student->name;
				$tmp["id"] = $student->id;
				$tmp["department_id"] = $student->department_id;
				$acStudent[] = $tmp;
			}
			$id=0;
			$invoice_no = "";
			$bulk_no = "";
			$invoice_date = date("d-M-Y");
			$student_id = "";
			$student_name = "";
			$department_id = "";			
			$notes="";
			$ttl_invoice= 0;
			$ttl_disc= 0;
			$invoice_type="Manual";
			$payment_type="CASH";
			$dInv = array();
			$autoId =0;
			$arrStudentTransactionType = MStudentTransactionType::model()->findAll("is_active = 1");
			$student_transaction_type_id ="";
			
			if ($op == "add"){
				$invoice_no = AutoNumber::getNewStudentInvoiceNo();				
			}else if ($op == "edit"){
				$id = Yii::app()->request->getParam("id","");				
				$hInv = MStudentHinvoice::model()->findByPK($id);
				if ($hInv == null){
					echo Yii::t("label","Invoice tidak di temukan");
					exit;
				}
				
				$invoice_no = $hInv->invoice_no;				
				$bulk_no = $hInv->bulk_no;
				$invoice_date = $hInv->invoice_date;
				$invoice_date =  date_format(date_create_from_format( "Y-m-d", $invoice_date),"d-M-Y");
				$student_transaction_type_id = $hInv->student_transaction_type_id;
				$student_id = $hInv->student_id;
				$student_name = $hInv->student->sid ." - " . $hInv->student->name;
				$department_id = $hInv->student->department_id;
				$notes=$hInv->notes;
				$ttl_invoice= $hInv->ttl_invoice;
				$ttl_disc= $hInv->ttl_disc;
				$invoice_type=$hInv->invoice_type;
				$payment_type="CASH";				
				$arrDInv = MStudentDinvoice::model()->findAll("invoice_id =:invId",array(":invId"=>$id));				
				foreach($arrDInv as $rw){
					$autoId +=1;
					$tmp=array();
					$tmp["id"] = $autoId;
					$tmp["student_item_id"] = $rw->student_item_id;
					$tmp["student_item_desc"] = $rw->studentItem->item_desc;
					$tmp["notes"]= $rw->notes;
					$tmp["total"]= $rw->total;
					$tmp["qty"]=$rw->qty;
					$tmp["disc"] = $rw->disc;
					$tmp["total_after_disc"]= ($rw->total * $rw->qty)- $rw->disc;
					$tmp["status"]="existing";
					$dInv[] =$tmp; 					
				}
				
			}
			$data["op"]= $op;
			$data["id"]=$id;
			$data["acStudent"]=CJSON::encode($acStudent);
			$data["invoice_no"]= $invoice_no;
			$data["bulk_no"]= $bulk_no;
			$data["invoice_date"]= $invoice_date;
			$data["student_transaction_type_id"]= $student_transaction_type_id;
			$data["student_id"]= $student_id;
			$data["student_name"]= $student_name;
			$data["department_id"]= $department_id;
			$data["notes"]= $notes;
			$data["ttl_invoice"]= $ttl_invoice;
			$data["ttl_disc"]= $ttl_disc;
			$data["invoice_type"]= $invoice_type;
			$data["payment_type"]= $payment_type;	
			$dInv = json_encode($dInv);
			$data["dInv"]= $dInv;
			$data["autoId"] = $autoId;
			$data["arrStudentTransactionType"] = $arrStudentTransactionType;
			
			$this->layout ="//layouts/ly_blank";
			$this->render('invForm',$data);
		}
		
		public function actionCnForm(){
			$errorMessage = array();
			$data = array();
			$id = Yii::app()->request->getParam("id",0);
			$op = Yii::app()->request->getParam("op","");
			
			if ($op != "delete"){
				$hInv = MStudentHinvoice::model()->find("id = :id and is_active = 1",array(":id"=>$id));
				if ($hInv == null){
					$errorMessage = Yii::t("label","Data invoice tidak di temukan!");
					$this->pageError($errorMessage);
				}
			}
			if($op == "add"){
				//add
				$errorMessage = Yii::t("label","Data tidak dapat di simpan..!");
				$notes = Yii::app()->request->getParam("notes","");
				$cn_date = Yii::app()->request->getParam("cn_date",date("d-M-Y"));
				$cn_date = date_format(date_create_from_format( "d-M-Y", $cn_date),"Y-m-d");
				
				$cn_acc_id = Yii::app()->request->getParam("cn_acc_id",0);
				$cek = MCoa::model()->find("id =:id and is_active = 1",array(":id"=>$cn_acc_id));
				if ($cek == null){
					$errorMessage = Yii::t("label","Account ID credit note tidak di kenal !");
					$this->pageError($errorMessage,	$studentCN->getErrors());
				}
				
				$ttl_cn =  Yii::app()->request->getParam("ttl_cn",0);
				$ttl_cn = Utils::parseCurrency($ttl_cn);
				
				$conn = Yii::app()->db;
				$transaction = $conn->beginTransaction();
				try{
					//Create Jurnal
					$tmp = Jurnal::createHeader("Credit Note ". $hInv->invoice_no , $cn_date);
					$jurnalId = $tmp["id"];
				
					$studentCN = new MStudentCreditNotes();
					$studentCN->cn_date = $cn_date;
					$studentCN->invoice_id = $id;
					$studentCN->cn_acc_id = $cn_acc_id;
					$studentCN->ttl_cn = $ttl_cn;
					$studentCN->notes = $notes;
					$studentCN->jurnal_id = $jurnalId;
					$studentCN->is_active = 1;
					$studentCN->input_datetime = date("Y-m-d H:i:s");
					$studentCN->user_id = $this->loginUser->id;				
					if(!$studentCN->save()){
						throw new Exception(json_encode($studentCN->getErrors()));
					}
				
					//Update Invoice
					$hInv->ttl_cn = $hInv->ttl_cn + $ttl_cn;
					if (!$hInv->save()){
						throw new Exception(json_encode($hInv->getErrors()));
					}
					$tmp = Jurnal::createDetail($jurnalId, $cn_acc_id, "", "D", $ttl_cn);
					$tmp = Jurnal::createDetail($jurnalId, $hInv->piutang_acc_id, "", "C", $ttl_cn);
					
				}catch (Exception $e){
					$transaction->rollback();
					$this->pageError($errorMessage,	$e->getMessage());
				}
				
				$transaction->commit();
				echo "<script>window.top.location.href='index.php?r=keuangan/tu/studentInvoice/cnForm&id=$id'</script>";
								
			}else if($op == "delete"){
				//id di sini merupaka id dari student credit notes
				$errorMessage = Yii::t("dialog","Hapus data credit notes, gagal !");
				$successMessage = Yii::t("dialog","Data credit notes, telah di hapus !");
				$studentCN = MStudentCreditNotes::model()->find("id = :id and is_active = 1",array(":id"=>$id));
				$conn = Yii::app()->db;
				$transaction = $conn->beginTransaction();
				try{
					$jurnalId = $studentCN->jurnal_id;
					//delete detail jurnal
					Jurnal::deleteDetailJurnal($jurnalId,$conn);				
					//update invoice
					$studentCN->invoice->ttl_cn = $studentCN->invoice->ttl_cn - $studentCN->ttl_cn;
					if (!$studentCN->invoice->save()){
						$transaction->rollback();
						$this->result["actionStatus"] =1;
						$this->result["actionMessage"] = $errorMessage;
						$this->result["actionError"] = $studentCN->invoice->getErrors();
						$this->returnJSON($this->result);
					}
					
					//delete CN
					if (!$studentCN->delete()){
						$transaction->rollback();
						$this->result["actionStatus"] =1;
						$this->result["actionMessage"] = $errorMessage;
						$this->result["actionError"] = $studentCN->getErrors();
						$this->returnJSON($this->result);
					}
					//delete header jurnal
					Jurnal::deleteHeaderJurnal($jurnalId,$conn);
					$transaction->commit();
					$this->result["actionStatus"] =0;
					$this->result["actionMessage"] = Yii::t("dialog",$successMessage);
					$this->returnJSON($this->result);
					
				}catch (Exception $e){
					$transaction->rollback();
					$this->result["actionStatus"] =1;
					$this->result["actionMessage"] = $errorMessage;
					$this->result["actionError"] = $e->getMessage();
					$this->returnJSON($this->result);
				}
				
			}
			
			
			$arrObCn = MStudentCreditNotes::model()->findAll("invoice_id = :id",array(":id"=>$id));
			$arrCn = array();
			foreach($arrObCn as $cn){
				$tmp = array();
				$tmp["id"]= $cn->id;
				$tmp["cn_date"]= $cn->cn_date;
				$tmp["invoice_id"]= $cn->invoice_id;
				$tmp["cn_acc_id"]= $cn->cn_acc_id;
				$tmp["cn_acc_name"]= $cn->cnAcc->acc_code ." - " .$cn->cnAcc->name;
				$tmp["ttl_cn"]= $cn->ttl_cn;
				$tmp["notes"]= $cn->notes;
				$tmp["is_active"]= $cn->is_active;
				$tmp["input_datetime"]= $cn->input_datetime;
				$tmp["user_id"]= $cn->user_id;
				$arrCn[] = $tmp;				
			}			
			//$arrCn = Utils::activeRecord2Array($arrCn);
			$data["arrCn"] = json_encode($arrCn);
			$data["hInv"] = $hInv;
			
			$arrCnCOA=MCoa::model()->findAll("is_active = 1 and acc_code like '4.%' and is_leaf = 1");
			$data["arrCnCOA"] = $arrCnCOA;
			
			$this->layout ="//layouts/ly_blank";
			$this->render('cnForm',$data);
		}
		
		
		public function actionInvoiceDetailForm(){
			$arrItem = MStudentItem::model()->findAll("is_active = 1");
			
			$acItem = array();
			foreach ($arrItem as $item){
				$tmp = array();
				$tmp["label"] = $item->item_desc;
				$tmp["value"] = $item->item_desc;
				$tmp["id"] = $item->id;
				$tmp["item_price"] = $item->item_price;
				$acItem[] = $tmp;
			}
				
			$data["acItem"]=CJSON::encode($acItem);
				
			$this->layout ="//layouts/ly_blank";
			$this->render('invDetailForm',$data);
		}
		public function actionEdit(){
			$strInvDetail = Yii::app()->request->getParam("invDetail","[]");			
			$arrInvDetail = json_decode($strInvDetail);
			$op = Yii::app()->request->getParam("op","");			
			$id = Yii::app()->request->getParam("id","");
			$invoice_no = Yii::app()->request->getParam("invoice_no","");
			$invoice_date = Yii::app()->request->getParam("invoice_date","01-Jan-2000");
			$invoice_date = date_format(date_create_from_format( "d-M-Y", $invoice_date),"Y-m-d");
			$student_transaction_type_id = Yii::app()->request->getParam("student_transaction_type_id","");
			$student_id = Yii::app()->request->getParam("student_id","");
			$notes = Yii::app()->request->getParam("notes","");
			$payment_type = Yii::app()->request->getParam("payment_type","");
			$userLogin = LoginUser::getUser();
			
			if ($op == "add"){
				$errorMessage = Yii::t("label","Transaksi gagal !");
				$transaction = Yii::app()->db->beginTransaction();
				try{
					$tmp = Jurnal::createHeader("",$invoice_date,"Auto");
					$jurnalId =$tmp["id"];
				
					$hInv = new MStudentHinvoice();
					$hInv->invoice_no = AutoNumber::getNewStudentInvoiceNo();
					$hInv->invoice_date =$invoice_date;
					$hInv->ttl_invoice = 0;
					$hInv->ttl_disc = 0;
					$hInv->ttl_paid = 0;
					$hInv->ttl_cn = 0;
					$hInv->payment_type =$payment_type;
					$hInv->jurnal_id= $jurnalId;
					$hInv->invoice_type ="Manual";
					$hInv->input_date_time=date("Y-m-d H:i:s");
					$hInv->user_id =$userLogin ->id;
					$hInv->is_active =1;
					$cek = MStudentTransactionType::model()->find("id = :studentTransTypeId and is_active = 1",array(":studentTransTypeId"=>$student_transaction_type_id));
					if ($cek == null){
						throw new Exception("Invalid Transaction Type");
					}
					$hInv->student_transaction_type_id = $student_transaction_type_id;
					$hInv->piutang_acc_id = $cek->piutang_acc_id;
					$student = MStudent::model()->find("id =:studentId and is_active = 1",array(":studentId"=>$student_id));
					if ($student == null){
						$errorMessage =Yii::t("label","Siswa tidak di kenal..!"); 
						throw new Exception("Invalid Student");
					}
					$hInv->student_id = $student_id;
					$hInv->notes = $notes;				
					if (!$hInv->save()){
						throw new Exception(json_encode($hInv->getErrors()));
					}
					
					//add Detail
					$ttlInv =0;
					$ttlDisc =0;				
					foreach ($arrInvDetail as $invDetail){
						$dInv = new MStudentDinvoice();
						$dInv->invoice_id = $hInv->id;
						$dInv->student_item_id = $invDetail->student_item_id;
						$dInv->notes= $invDetail->notes;
						$dInv->qty = $invDetail->qty;
						$dInv->total = $invDetail->total;
						$dInv->disc = $invDetail->disc;	
	
						$ttlInv += $dInv->total * $dInv->qty;
						$ttlDisc += $dInv->disc;
						
						$item = MStudentItem::model()->findByPK($invDetail->student_item_id);
						if ($item == null){
							$errorMessage= Yii::t("label","Item tidak di kenal..");
							throw new Exception("Invalid Student item");
						}
						$dInv->disc_acc_id =$item->disc_acc_id ;
						$dInv->pendapatan_acc_id =$item->pendapatan_acc_id ;						
						//add jurnal
						
						$tmp = Jurnal::createDetail($jurnalId, $hInv->piutang_acc_id, "", "D", ($invDetail->total * $invDetail->qty) - $invDetail->disc);
						$tmp = Jurnal::createDetail($jurnalId, $dInv->disc_acc_id, "", "D", $invDetail->disc);
						$tmp = Jurnal::createDetail($jurnalId, $dInv->pendapatan_acc_id, "", "C", ($invDetail->total * $invDetail->qty));
						
						if (!$dInv->save()){
							throw new Exception(json_encode($dInv->getErrors()));
						}
					} //end foreach detail
					$hInv->ttl_invoice = $ttlInv;
					$hInv->ttl_disc = $ttlDisc;
					if(!$hInv->save()){
						throw new Exception(json_encode($hInv->getErrors()));
					}
				}catch (Exception $e){
					$transaction->rollback();
					$this->pageError($errorMessage,$e->getMessage(),$e);
				}
				$transaction->commit();
				echo"<script type='text/javascript'>alert('".Yii::t("dialog","Data telah di simpan!")."');window.close();</script>";
			}elseif ($op=="edit"){
				
				/*
				 * 1. cek payment status
				 * 2. delete detail jurnal
				 * 3. delete detail invoice
				 * 4. add new detail invoice
				 * 5. add new detail Jurnal
				 * 5. update header invoice (total_inv,total_disc)				 
				 */
					
				$errorMessage = Yii::t("label","Transaksi gagal !");
				$conn = Yii::app()->db;
				$transaction = $conn->beginTransaction();
				try{
					$hInv = MStudentHinvoice::model()->find("id =:id and invoice_type != 'Auto' ",array(":id"=>$id));
					if($hInv == null){
						$errorMessage = Yii::t("label","Data Invoice tidak di temukan.");
						throw new Exception("Invalid invoice id or invoice type is Auto");
					} 
					if(($hInv->ttl_paid + $hInv->ttl_cn)  != 0){
						$errorMessage = Yii::t("label","Invoice tidak dapat di rubah karena sudah ada transaksi pembayaran!");
						throw new Exception("Invoice already paid");						
					}
					
					$hInv->invoice_date =$invoice_date;
					$hInv->payment_type =$payment_type;
					$hInv->student_transaction_type_id =$student_transaction_type_id;
					$hInv->invoice_type ="Manual";
					$hInv->input_date_time=date("Y-m-d H:i:s");
					$hInv->user_id =$userLogin ->id;
					$hInv->is_active =1;
					$student = MStudent::model()->find("id =:studentId and is_active = 1",array(":studentId"=>$student_id));
					if ($student == null){
						$errorMessage = Yii::t("label","Siswa tidak di kenal..!");
						throw new Exception("Invalid student");
					}
					$hInv->student_id = $student_id;
					$hInv->notes = $notes;
					if (!$hInv->save()){
						throw new Exception(json_encode($hInv->getErrors()));
					}
					$ttlInv = 0;
					$ttlDisc = 0;					
					
					//delete all Detail Jurnal
					Jurnal::deleteDetailJurnal($hInv->jurnal_id,$conn);
					
					//delete all Detail Invoice
					$ssql = "delete from m_student_dinvoice where invoice_id = :invId";
					$cmd = $conn->createCommand($ssql);
					$cmd->execute(array(":invId"=>$hInv->id));
					
					foreach ($arrInvDetail as $invDetail){
						$dInv = new MStudentDinvoice();
						$dInv->invoice_id = $hInv->id;
						
						$item = MStudentItem::model()->findByPK($invDetail->student_item_id);
						if ($item == null){
							$errorMessage = Yii::t("label","Item tidak di kenal..");
							throw new Exception("Invalid student item :".$invDetail->student_item_id);
						}
						$dInv->student_item_id = $invDetail->student_item_id;
						$dInv->notes= $invDetail->notes;
						$dInv->qty = $invDetail->qty;
						$dInv->total = $invDetail->total;
						$dInv->disc = $invDetail->disc;
						$dInv->disc_acc_id = $item->disc_acc_id;
						$dInv->pendapatan_acc_id = $item->pendapatan_acc_id;
						if(!$dInv->save()){
							throw new Exception(json_encode($dInv->getErrors()));
						}
						
						$ttlInv += $dInv->total * $dInv->qty;
						$ttlDisc += $dInv->disc;												
						$tmp = Jurnal::createDetail($hInv->jurnal_id, $hInv->piutang_acc_id, "", "D", ($invDetail->total * $invDetail->qty) - $invDetail->disc);
						$tmp = Jurnal::createDetail($hInv->jurnal_id, $dInv->disc_acc_id, "", "D",$invDetail->disc);
						$tmp = Jurnal::createDetail($hInv->jurnal_id, $dInv->pendapatan_acc_id, "", "C", ($invDetail->total * $invDetail->qty));
					}
					
					$hInv->ttl_invoice = $ttlInv;
					$hInv->ttl_disc = $ttlDisc;
					if(!$hInv->save()){
						throw new Exception(json_encode($hInv->getErrors()));
					}
				}catch (Exception $e){
					$transaction->rollback();
					$this->pageError($errorMessage,$e->getMessage(),$e);
				}				
				$transaction->commit();
				echo"<script type='text/javascript'>alert('".Yii::t("dialog","Data telah di simpan!")."');window.close();</script>";
							
			}else if($op == "delete"){
				$errorMessage = Yii::t("label","Transaksi gagal !");
				$successMessage = Yii::t("label","Invoice telah dihapus !");
				$id = Yii::app()->request->getParam("id",0);
				$conn = Yii::app()->db;
				$transaction = $conn->beginTransaction();				
				try{
					$hInv = MStudentHinvoice::model()->findByPK($id);
					if ($hInv == null){
						$errorMessage = Yii::t("label","Invoice tidak di temukan!");
						throw new Exception("Invalid Invoice id");
					}
					
					if ($hInv->invoice_type == "Auto"){
						$errorMessage = Yii::t("label","Invoice dengan type AUTO tidak dapat di hapus!");
						throw new Exception("Can't delete invoice with type AUTO");
					}
						
					if(($hInv->ttl_paid + $hInv->ttl_cn) != 0){
						$errorMessage = Yii::t("label","Invoice tidak dapat di hapus karena sudah ada transaksi pembayaran!");
						throw new Exception("Invoice already paid");
					}
					
					
					//delete detail jurnal
					Jurnal::deleteDetailJurnal($hInv->jurnal_id,$conn);
					
					//delete detail invoice
					$ssql = "delete from m_student_dinvoice where invoice_id = " . $hInv->id;
					$cmd = $conn->createCommand($ssql);
					$cmd->execute();
						
					//delete header invoice & recounting total bulk if exist
					$jurnalId =$hInv->jurnal_id;  
					if ($hInv->bulk_no){
						$bulk = MStudentHinvoiceBulk::model()->find("bulk_no =:bulkNo" ,array(":bulkNo"=>$hInv->bulk_no));
						if ($bulk != null){
							$bulk->total_invoice -=1;
							if(!$bulk->save()){
								throw new Exception(json_encode($bulk->getErrors()));
							}
						}
					}
					if(!$hInv->delete()){
						throw new Exception(json_encode($hInv->getErrors()));
					}	
					
					//delate header jurnal
					Jurnal::deleteHeaderJurnal($jurnalId,$conn);
					
					$transaction->commit();
					$this->result["actionStatus"] = 0;
					$this->result["actionMessage"] = $successMessage;
					$this->result["actionError"] ="";
					$this->returnJSON($this->result);
					
				}catch (Exception $e){
					$transaction->rollback();
					$this->result["actionStatus"] = 2;
					$this->result["actionMessage"] = $errorMessage;
					$this->result["actionError"] = $e->getMessage();
					$this->returnJSON($this->result);
				}
				
			}			
		}
		public function actionOutstandingDetailList(){
			$department_id = Yii::app()->request->getParam("department_id","");
			//$startDate = Yii::app()->request->getParam("start_date","");
			//$startDate = date_format(date_create_from_format( "d-M-Y", Yii::app()->request->getParam("start_date","01-Jan-2000")),"Y-m-d");			
			//$endDate= Yii::app()->request->getParam("end_date","");
			//$endDate = date_format(date_create_from_format( "d-M-Y", Yii::app()->request->getParam("end_date","01-Jan-3000")),"Y-m-d");
			
			$arrStudentHInv = MStudentHinvoice::model()->with(
					array(
						"student"=>array("alias"=>"a"),
					)
				)->findAll(
					array(
						"condition"=>"a.department_id = :deptId and (ttl_paid + ttl_cn)  < (ttl_invoice -ttl_disc)",
						"params"=>array(":deptId"=>$department_id),
						"order"=>"t.id desc"
					));

			// rebuilt model
			
			$arrModel = array();
			foreach($arrStudentHInv as $studentHInv){
				//colNames: ['id','SID','Name','Invoice no','Date','Total','Disc','Paid','Type', 'Keterangan', 'Status','Action'],
				$arrTmp = array();
				$arrTmp["id"] = $studentHInv->id;
				$arrTmp["sid"] = $studentHInv->student->sid;
				$arrTmp["student_name"] = $studentHInv->student->name;
				$arrTmp["invoice_no"] = $studentHInv->invoice_no;
				$arrTmp["bulk_no"] = $studentHInv->bulk_no;
				$arrTmp["invoice_date"] = $studentHInv->invoice_date;
				$arrTmp["total_invoice"] = $studentHInv->ttl_invoice;
				$arrTmp["disc"] = $studentHInv->ttl_disc;
				$arrTmp["total"] = $studentHInv->ttl_invoice - $studentHInv->ttl_disc;
				$arrTmp["total_paid"] = $studentHInv->ttl_paid;
				$arrTmp["total_cn"] = $studentHInv->ttl_cn;
				$arrTmp["invoice_type"] = $studentHInv->invoice_type;
				$arrTmp["notes"] = $studentHInv->notes;
				$arrTmp["is_active"] = $studentHInv->is_active;				
				$arrModel[] = $arrTmp;
			}
			$this->result["actionStatus"] = 0;
			$this->result["model"] = $arrModel;
			$this->returnJSON($this->result);
			
		}
		public function actionOutstandingStudentDetailList(){
			$student_id = Yii::app()->request->getParam("student_id","");
			$arrStudentHInv = MStudentHinvoice::model()->findAll(
					array(
							"condition"=>"student_id = :studentId and (ttl_paid + ttl_cn)  < (ttl_invoice -ttl_disc)",
							"params"=>array(":studentId"=>$student_id),
							"order"=>"id desc"
					));
		
			// rebuilt model
				
			$arrModel = array();
			foreach($arrStudentHInv as $studentHInv){
				//colNames: ['id','SID','Name','Invoice no','Date','Total','Disc','Paid','Type', 'Keterangan', 'Status','Action'],
				$arrTmp = array();
				$arrTmp["id"] = $studentHInv->id;
				$arrTmp["invoice_no"] = $studentHInv->invoice_no;
				$arrTmp["bulk_no"] = $studentHInv->bulk_no;
				$arrTmp["invoice_date"] = $studentHInv->invoice_date;
				$arrTmp["total_invoice"] = $studentHInv->ttl_invoice;
				$arrTmp["disc"] = $studentHInv->ttl_disc;
				$arrTmp["total"] = $studentHInv->ttl_invoice - $studentHInv->ttl_disc;
				$arrTmp["total_paid"] = $studentHInv->ttl_paid;
				$arrTmp["total_cn"] = $studentHInv->ttl_cn;
				$arrTmp["invoice_type"] = $studentHInv->invoice_type;
				$arrTmp["notes"] = $studentHInv->notes;
				$arrTmp["is_active"] = $studentHInv->is_active;
				$arrModel[] = $arrTmp;
			}
			$this->result["actionStatus"] = 0;
			$this->result["model"] = $arrModel;
			$this->returnJSON($this->result);
				
		}
		public function actionSettledDetailList(){
			$department_id = Yii::app()->request->getParam("department_id","");
			$startDate = Yii::app()->request->getParam("start_date","");
			$startDate = date_format(date_create_from_format( "d-M-Y", Yii::app()->request->getParam("start_date","01-Jan-2000")),"Y-m-d");
			$endDate= Yii::app()->request->getParam("end_date","");
			$endDate = date_format(date_create_from_format( "d-M-Y", Yii::app()->request->getParam("end_date","01-Jan-3000")),"Y-m-d");
			$arrStudentHInv = MStudentHinvoice::model()->with(
					array(
							"student"=>array("alias"=>"a"),
					)
			)->findAll(
					array(
							"condition"=>"a.department_id = :deptId and invoice_date >= :startDate and invoice_date <= :endDate and (ttl_invoice - ttl_disc) <= (ttl_paid + ttl_cn) ",
							"params"=>array(":deptId"=>$department_id,":startDate"=>$startDate,":endDate"=>$endDate),
							"order"=>"t.id desc"
					));
		
			// rebuilt model
				
			$arrModel = array();
			foreach($arrStudentHInv as $studentHInv){
				//colNames: ['id','SID','Name','Invoice no','Date','Total','Disc','Paid','Type', 'Keterangan', 'Status','Action'],
				$arrTmp = array();
				$arrTmp["id"] = $studentHInv->id;
				$arrTmp["sid"] = $studentHInv->student->sid;
				$arrTmp["student_name"] = $studentHInv->student->name;
				$arrTmp["invoice_no"] = $studentHInv->invoice_no;
				$arrTmp["bulk_no"] = $studentHInv->bulk_no;
				$arrTmp["invoice_date"] = $studentHInv->invoice_date;
				$arrTmp["total"] = $studentHInv->ttl_invoice - $studentHInv->ttl_disc;
				$arrTmp["paid"] = $studentHInv->ttl_paid;
				$arrTmp["cn"] = $studentHInv->ttl_cn;
				$arrTmp["invoice_type"] = $studentHInv->invoice_type;
				$arrTmp["notes"] = $studentHInv->notes;
				$arrTmp["is_active"] = $studentHInv->is_active;
				$arrModel[] = $arrTmp;
			}
			$this->result["actionStatus"] = 0;
			$this->result["model"] = $arrModel;
			$this->returnJSON($this->result);
				
		}
		
		public function actionIndexBulk(){
			$this->layout ="//layouts/ly_blank";
			$this->render('indexBulk');
		}
		
		public function actionBulkDetailList(){
			$department_id = Yii::app()->request->getParam("department_id","");
			$startDate = Yii::app()->request->getParam("start_date","");
			$startDate = date_format(date_create_from_format( "d-M-Y", Yii::app()->request->getParam("start_date","01-Jan-2000")),"Y-m-d");			
			$endDate= Yii::app()->request->getParam("end_date","");
			$endDate = date_format(date_create_from_format( "d-M-Y", Yii::app()->request->getParam("end_date","01-Jan-3000")),"Y-m-d");
						
			$arrHInvBulk = MStudentHinvoiceBulk::model()->findAll("department_id =:deptId and invoice_date >= :startDate and invoice_date <= :endDate order by id desc",array(":deptId"=>$department_id,":startDate"=>$startDate,":endDate"=>$endDate));
			$arrModel = array();
			foreach($arrHInvBulk as $hInvBulk){
				$arrTmp = array();
				$arrTmp["id"] = $hInvBulk->id;
				$arrTmp["department_id"] = $hInvBulk->department_id;
				$arrTmp["bulk_no"] = $hInvBulk->bulk_no;
				$arrTmp["invoice_date"] = $hInvBulk->invoice_date;
				$arrTmp["notes"] = $hInvBulk->notes;
				$arrTmp["total_invoice"] = $hInvBulk->total_invoice;
				//$arrHInv = MStudentHinvoice::model()->findAll("bulk_no =:bulkNo and is_active = 1",array(":bulkNo"=>$hInvBulk->bulk_no));				
				//$arrTmp["ttl_invoice"] = count($arrHInv);				
				$arrModel[] = $arrTmp;
			}
			$this->result["actionStatus"] = 0;
			$this->result["model"] = $arrModel;
			$this->returnJSON($this->result);
		}
		public function actionInvoiceBulkForm(){
			$op = "add"; //no edit function
			$id=0;
			$bulk_no = "";
			$invoice_date = date("d-M-Y");
			$notes="";
			
			
			$arrDepartment = MDepartment::model()->findAll("is_active = 1");
			$department_id ="";
			if ($arrDepartment !=null){
				$dept = $arrDepartment[0];
				$department_id  = $dept->id; 
			}
			
			
			$tahun_ajaran = CMTahunAjaran::getTahunAjaranActive($department_id);
				
			//$arrTingkatan = MTingkatan::model()->findAll("department_id ='$department_id' and is_active = 1");
			$ssql ="select distinct b.tingkatan_id as id,c.name as tingkatan_name from m_kelas_tahun_ajaran a 
					inner join m_kelas b on a.kelas_id = b.id 
					inner join m_tingkatan c on b.tingkatan_id = c.id
					where a.is_active = 1 and b.is_active = 1 and c.is_active = 1 and a.tahun_ajaran_id = " . $tahun_ajaran->id;
			$conn = Yii::app()->db;
			$cmd = $conn->createCommand($ssql);
			$arrTingkatan = $cmd->queryAll();
			
			/*
			$arrTingkatan = MKelasTahunAjaran::model()->with(array(
						"kelas"=>array("alias"=>"a"),
						"kelas.tingkatan"=>array("alias"=>b)
					))->findAll("b.is_active = 1 and a.is_active = 1");
			*/
									
			$tingkatan_id = "";			
			if ($arrTingkatan !=null){
				$tingkatan = $arrTingkatan[0];
				$tingkatan_id  = $tingkatan["id"];
			}
			
			$arrKelasTahunAjaran = MKelasTahunAjaran::model()->with("kelas")->findAll("tahun_ajaran_id = $tahun_ajaran->id and kelas.tingkatan_id = $tingkatan_id");
			
			//get data student
			$ssql = "select b.id,b.sid,b.name as student_name,d.name as kelas_name,'CASH' as payment_type from m_student_class a 
					inner join m_student b on a.student_id  =b.id
					inner join m_kelas_tahun_ajaran c on a.kelas_tahun_ajaran_id = c.id
					inner join m_kelas d on c.kelas_id = d.id 
					where a.is_active = 1 and d.is_active = 1 and c.is_active = 1 and d.is_active = 1 and
					d.tingkatan_id = $tingkatan_id and c.tahun_ajaran_id = " . $tahun_ajaran->id ." order by sid ";
			 
			$cmd = $conn->createCommand($ssql);
			$arrStudent = $cmd->queryAll();
			
			$bulk_no = AutoNumber::getMonthlyFeeBulkNo();
			
			$arrStudentTransactionType = MStudentTransactionType::model()->findAll("is_active = 1");
			
			
			$data["op"]= $op;
			$data["id"]=$id;
			$data["bulk_no"]= $bulk_no;
			$data["arrStudentTransactionType"] = $arrStudentTransactionType;
			$data["invoice_date"]= $invoice_date;
			$data["arrDepartment"] = $arrDepartment;
			$data["arrTingkatan"] = $arrTingkatan;
			$data["arrKelasTahunAjaran"] = $arrKelasTahunAjaran; 
			$data["arrStudent"] = json_encode($arrStudent);
			$data["notes"]= $notes;
			
			//$dInvBulk = json_encode($dInvBulk);
			//$data["dInvBulk"]= $dInvBulk;
				
			$this->layout ="//layouts/ly_blank";
			$this->render('invBulkForm',$data);
		}
		
		public function actionEditBulk(){
			$id = Yii::app()->request->getParam("id","");
			$op = Yii::app()->request->getParam("op","");
			if ($op == "add"){
				try{
					$errorMessage = Yii::t("label","Transaksi tidak dapat di simpan !");
					$department_id = Yii::app()->request->getParam("department_id","");
					$tingkatan_id = Yii::app()->request->getParam("tingkatan_id","");
					$kelas_tahun_ajaran_id= Yii::app()->request->getParam("kelas_tahun_ajaran_id","");			
					$active_tahun_ajaran = CMTahunAjaran::getTahunAjaranActive($department_id);			
					$strInvDetail = Yii::app()->request->getParam("invDetail","[]");
					$arrInvDetail = json_decode($strInvDetail);
					if (count($arrInvDetail) == 0){
						$errorMessage = Yii::t("label","Tidak terdapat item dalam trasaksi");
						throw new Exception("No item added on this transaction ");
					}
					
					$strInvStudentList =  Yii::app()->request->getParam("invStudentList","[]");
					$arrInvStudent= json_decode($strInvStudentList);
					if (count($arrInvStudent) == 0){
						$errorMessage = Yii::t("label","Tidak ada student yang di pilih");
						throw new Exception("No selected student");
					}
					
					$bulk_no = Yii::app()->request->getParam("bulk_no","");
					$student_transaction_type_id = Yii::app()->request->getParam("student_transaction_type_id",0);
					$studentTransType = MStudentTransactionType::model()->find("id = :studentTransTypeId and is_active = 1",array(":studentTransTypeId"=>$student_transaction_type_id));
					if($studentTransType == null){
						$errorMessage = Yii::t("label","Jenis transaksi tidak di kenal !");
						throw new Exception("Invalid transaction type");
					}
					
					$piutang_acc_id = $studentTransType->piutang_acc_id;					
					$invoice_date = Yii::app()->request->getParam("invoice_date","01-Jan-2000");
					$invoice_date = date_format(date_create_from_format( "d-M-Y", $invoice_date),"Y-m-d");
					$notes = Yii::app()->request->getParam("notes","");			
					$conn = Yii::app()->db;
					$transaction = $conn->beginTransaction();
					$ttlStudent =0;
					
					foreach ($arrInvStudent as $student){
						$ttlStudent +=1;
						//create jurnal header
						$tmp = Jurnal::createHeader($bulk_no, $invoice_date,"Auto");
						$jurnalId =$tmp["id"];
						//Create Student Invoice
						$hInv = new MStudentHinvoice();
						$hInv->invoice_no = AutoNumber::getNewStudentInvoiceNo();
						$hInv->bulk_no = $bulk_no;
						$hInv->student_transaction_type_id = $student_transaction_type_id;
						$hInv->invoice_date = $invoice_date; 
						$hInv->student_id = $student->id;
						$hInv->notes = $notes;
						$hInv->ttl_invoice =0;
						$hInv->ttl_disc =0;
						$hInv->ttl_paid =0;
						$hInv->ttl_cn =0;
						$hInv->payment_type = $student->payment_type;
						$hInv->jurnal_id = $jurnalId;
						$hInv->piutang_acc_id = $piutang_acc_id;
						$hInv->invoice_type ="Manual";
						$hInv->input_date_time = date("Y-m-d H:i:s");
						$userLogin = LoginUser::getUser();
						$hInv->user_id =$userLogin->id; 
						$hInv->is_active = 1;
						if(!$hInv->save()){
							throw new Exception(json_encode($hInv->getErrors()));
						}					
						$ttlInvoice =0;
						$ttlDisc =0;
						foreach ($arrInvDetail as $invDetail){
							$ttlInvoice += $invDetail->total * $invDetail->qty;
							$ttlDisc += $invDetail->disc;
							
							// add detail Invoice
							$dInv = new MStudentDinvoice();
							$dInv->invoice_id = $hInv->id;
							
							$studentItem = MStudentItem::model()->findByPK($invDetail->student_item_id); 
							$dInv->student_item_id = $invDetail->student_item_id;
							$dInv->disc_acc_id = $studentItem->disc_acc_id;
							$dInv->pendapatan_acc_id = $studentItem->pendapatan_acc_id;						
							$dInv->notes = $invDetail->notes;
							$dInv->qty = $invDetail->qty;
							$dInv->total = $invDetail->total;
							$dInv->disc = $invDetail->disc;
							if(!$dInv->save()){
								throw new Exception(json_encode($dInv->getErrors()));
							}
							
							//add detail jurnal
							$tmp = Jurnal::createDetail($jurnalId, $hInv->piutang_acc_id, "", "D", ($dInv->total * $dInv->qty) - $dInv->disc);
							$tmp = Jurnal::createDetail($jurnalId, $dInv->disc_acc_id, "", "D", $dInv->disc);
							$tmp = Jurnal::createDetail($jurnalId, $dInv->pendapatan_acc_id, "", "D", ($dInv->total * $dInv->qty));
						} //end foreach detail invoice

						// update total;
						$hInv->ttl_invoice =$ttlInvoice;
						$hInv->ttl_disc =$ttlDisc;
						if(!$hInv->save()){
							throw new Exception(json_encode($hInv->getErrors()));
						}				
					} //end foreach list student					
					//add bulk log
					$hBulk = new MStudentHinvoiceBulk();
					$hBulk->department_id = $department_id;
					$hBulk->bulk_no = $bulk_no;
					$hBulk->notes = $notes;
					$hBulk->invoice_date = $invoice_date;
					$hBulk->input_date_time = date("Y-m-d");
					$userLogin = LoginUser::getUser();
					$hBulk->user_id = $userLogin->id;
					$hBulk->total_invoice = $ttlStudent;
					if (!$hBulk->save()){
						throw new Exception(json_encode($hBulk->getErrors()));
					}
				}catch (Exception $e){
					$transaction->rollback();
					$this->pageError($errorMessage,$e->getMessage(),$e);						
				}				
				$transaction->commit();
				echo"<script type='text/javascript'>alert(' $ttlStudent ".Yii::t("dialog","Invoice telah di buat!")."');window.close();</script>";
			}else if($op =="delete"){
				$errorMessage = Yii::t("dialog","Transaksi gagal di hapus !");
				$successMessage = Yii::t("dialog","Transaksi telah di hapus !");
				
				try{					
					$studentBulk = MStudentHinvoiceBulk::model()->find("id =:id",array(":id"=>$id));
					if ($studentBulk == null){
						throw new Exception("Invalid Bulk ID!");
					} 					
					$conn = Yii::app()->db;
					$transaction = $conn->beginTransaction();				
					$arrHInv = MStudentHinvoice::model()->findAll("bulk_no = :bulkNo",array(":bulkNo"=>$studentBulk->bulk_no));
					foreach($arrHInv as $hInv){
						$jurnalId = $hInv->jurnal_id;
						//cek Payment
						if (($hInv->ttl_paid + $hInv->ttl_cn)  > 0){
							$errorMessage = Yii::t("dialog",'Transaksi invoice $invoiceNo tidak dapat di hapus karena sudah dilakukan pembayaran!');
							$errorMessage = str_replace('$invoiceNo', $hInv->invoice_no, $errorMessage);
							throw new Exception("Invoice already paid !");
						}
						
						//delete detail jurnal & detail Invoice
						Jurnal::deleteDetailJurnal($jurnalId,$conn);						
						$ssql = "delete from m_student_dinvoice where invoice_id = " .$hInv->id;
						$cmd = $conn->createCommand($ssql);
						$cmd->execute();
						
						//delete Header Invoice & header jurnal
						if(!$hInv->delete()){
							throw new Exception(json_encode($hInv->getErrors()));
						}
						Jurnal::deleteHeaderJurnal($jurnalId,$conn);
					} //end foreach arrHInv
					
					if(!$studentBulk->delete()){
						throw new Exception(json_encode($studentBulk->getErrors()));
					}
					$transaction->commit();
					$this->result["actionStatus"] = 0;
					$this->result["actionMessage"] = $successMessage;
					$this->returnJSON($this->result);
				}catch (Exception $e){
					$transaction->rollback();
					$this->result["actionStatus"] = 1;
					$this->result["actionMessage"] = $errorMessage;
					$this->result["actionError"] = $e->getMessage();
					$this->returnJSON($this->result);
				}
				
			}			
		}
		
		public function actionCalculateActiveTotalStudent(){
			$department_id = Yii::app()->request->getParam("department_id","");
			$tingkatan_id = Yii::app()->request->getParam("tingkatan_id","");
			$kelas_tahun_ajaran_id= Yii::app()->request->getParam("kelas_tahun_ajaran_id","");			
			$active_tahun_ajaran = CMTahunAjaran::getTahunAjaranActive($department_id);
			
			if ($kelas_tahun_ajaran_id == ""){
				//all				
				$ssql = "select f.id,f.sid,f.name as student_name,c.name as kelas_name,f.payment_method as payment_type from m_student_class a 
						inner join m_kelas_tahun_ajaran b on a.kelas_tahun_ajaran_id = b.id
						inner join m_kelas c on b.kelas_id = c.id 
						inner join m_tingkatan d on c.tingkatan_id = d.id
						inner join m_department e on d.department_id = e.id
						inner join m_student f on a.student_id = f.id where 
						a.is_active = 1 and b.is_active = 1 and c.is_active = 1 and d.is_active = 1 and e.is_active = 1 and f.is_active = 1 and 
						c.tingkatan_id =:tingkatanId and b.tahun_ajaran_id =:tahunAjaranId";
				$params = array(":tingkatanId" =>$tingkatan_id,":tahunAjaranId"=>$active_tahun_ajaran->id);
								
			}else{
				$ssql = "select f.id,f.sid,f.name as student_name,c.name as kelas_name,f.payment_method as payment_type from m_student_class a 
						inner join m_kelas_tahun_ajaran b on a.kelas_tahun_ajaran_id = b.id
						inner join m_kelas c on b.kelas_id = c.id 
						inner join m_tingkatan d on c.tingkatan_id = d.id
						inner join m_department e on d.department_id = e.id
						inner join m_student f on a.student_id = f.id where 
						a.is_active = 1 and b.is_active = 1 and c.is_active = 1 and d.is_active = 1 and e.is_active = 1 and f.is_active = 1 and 
						b.id = :kelasTahunAjaranId";
				$params = array(":kelasTahunAjaranId" =>$kelas_tahun_ajaran_id);
			}
			$conn = Yii::app()->db;
			$cmd = $conn->createCommand($ssql);
			$rs = $cmd->queryAll(true,$params);
			$this->returnJSON($rs);
		}
		
		public function actionPrintInvoice(){
			$errorMessage = Yii::t("label","Data invoice tidak di temukan !");
			$id = Yii::app()->request->getParam("id","");
			$hInvoice = MStudentHinvoice::model()->find("id = :invoiceId and is_active = 1",array(":invoiceId"=>$id));
			if ($hInvoice == null){
				$this->pageError($errorMessage,"Invoice id not found");
			}
				
			$arrDInvoice = MStudentDinvoice::model()->findAll("invoice_id = :invoiceId",array(":invoiceId"=>$id));
			$data["id"] = $id;
			$data["hInvoice"] = $hInvoice;
			$data["arrDInvoice"] = $arrDInvoice;			
			$this->layout ="//layouts/ly_blank";
			$this->render('printInvoice',$data);
		}
		
		protected function beforeRender($view){
			if ($this->subMenu != ""){
				$this->title = $this->mainMenu . " - " .$this->subMenu;
			}
			return true;
		}
		
	}
?>